﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using DevExpress.Spreadsheet;
using DevExpress.XtraSpreadsheet;
using QL_Truong_Hoc;

namespace QL_Truong_Hoc.Export
{
    class clsDiemNghe
    {

        //Data table đầu vào cần có những trường sau:
        //kh_ten
        //khs_tenkhoas
        //Lop_ID
        //Lop_ten
        //mo_tenmon
        //LyThuyet
        //ThucHanh
        //KiemTra
        //SoDiemDinhKi

        //hs_hodem
        //hs_ten
        //hs_ngaysinh

        //HSM_diemdinhkylanso1
        //HSM_diemdinhkylanso2
        //HSM_diemdinhkylanso3
        //HSM_diemdinhkylanso4
        //HSM_diemdinhkylanso5
        //HSM_diemdinhkylanso6
        //HSM_diemdinhkylanso7
        //HSM_diemdinhkylanso8
        //HSM_diemdinhkylanso9
        //HSM_diemdinhkylanso10
        //HSM_diemketthucmodulelan1
        //HSM_diemketthucmodulelan2
        //HSM_diemtongket
        //HSM_ghichu

        private string mcstrTemplate = Environment.CurrentDirectory + @"\Templates\Mau 15.3_Bang diem kiem tra ket thuc mon hoc.xls";

        public clsDiemNghe()
        {
        }


        /// <summary>
        /// Thực hiện xuất ra file excel.
        /// File template là file đã định sẵn.
        /// </summary>
        /// <param name="objData">Dữ liệu đưa vào file word</param>
        public bool fncExport(DataTable objData)
        {
            bool blnResult = false;

            try
            {
                //get data
                SpreadsheetControl objExcel = new SpreadsheetControl();
                objExcel.LoadDocument(mcstrTemplate);

                //xExport(objExcel, objData, false);

                return true;
            }
            catch (Exception ex) { clsCommon.fncMsgError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message); }
            finally { }
            return blnResult;
        }


        /// <summary>
        /// Thực hiện xuất ra file excel.
        /// </summary>
        /// <param name="SpreadsheetControl">excel word</param>
        /// <param name="objData">Dữ liệu đầu vào</param>
        /// <param name="blnShowMergedData">Có hiện dữ liệu đã xuất ngay trên control không?</param>
        /// <returns></returns>
        public bool fncExport(SpreadsheetControl objExcelControl, DataTable objData,  bool blnShowMergedData = false)
        {
            bool blnResult = false;

            try
            {
                //get data
                return xExport(objExcelControl, objData,  blnShowMergedData);
            }
            catch (Exception ex) { clsCommon.fncMsgError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message); }
            finally { }
            return blnResult;
        }


        /// <summary>
        /// Phương thức cơ sở thực hiện mail merge
        /// </summary>
        /// <param name="objExcelControl">control excel</param>
        /// <param name="objData">dữ liệu vào</param>
        /// <param name="blnShowMergedData">có xem kết quả không?</param>
        /// <returns></returns>
        private bool xExport(SpreadsheetControl objExcelControl, DataTable objData, bool blnShowMergedData = false)
        {
            bool blnResult = false;

            try
            {
                if (objData == null) return false;

                if (!System.IO.File.Exists(mcstrTemplate))
                {
                    clsCommon.fncMsgWarning("Không tìm thấy tệp tin mẫu.");
                    return false;
                }

                objExcelControl.LoadDocument(mcstrTemplate);

                string strkh_ten = "";
                string strkhs_tenkhoas = "";
                string strLop_ten = "";
                string strmo_tenmon = "";

                int intLyThuyet = 0;
                int intThucHanh = 0;
                int intKiemTra = 0;
                int intSoDiemDinhKi = 10;

                string strhs_hodem;
                string strhs_ten;
                DateTime dths_ngaysinh;

                float fltHSM_diemdinhkylanso1;
                float fltHSM_diemdinhkylanso2;
                float fltHSM_diemdinhkylanso3;
                float fltHSM_diemdinhkylanso4;
                float fltHSM_diemdinhkylanso5;
                float fltHSM_diemdinhkylanso6;
                float fltHSM_diemdinhkylanso7;
                float fltHSM_diemdinhkylanso8;
                float fltHSM_diemdinhkylanso9;
                float fltHSM_diemdinhkylanso10;
                float fltHSM_diemketthucmodulelan1;
                float fltHSM_diemketthucmodulelan2;
                float fltHSM_diemtongket;

                string strHSM_ghichu;

                int intCurrentRow = 14;
                int intCurrentCol = 0;

                const string cstrFloatFormat = "0.0";

                //tạo n dong moi.
                if (objData.Rows.Count > 3)
                {
                    objExcelControl.ActiveWorksheet.Rows.Insert(16, objData.Rows.Count - 3);

                    //(12 + objData.Rows.Count - 2) -> bắt đầu tính từ dòng 12 + (Số bản ghi - 1 là 2 dòng đầu tiên bị nhảy qua + 1 dòng cuối ko copy format)
                    objExcelControl.ActiveWorksheet.Range["A17:Q" + (16 + objData.Rows.Count - 3)].CopyFrom(objExcelControl.ActiveWorksheet.Range["A16:Q16"]);
                }

                //lấy dòng đầu tiên fill header
                if (objData.Rows.Count > 0)
                {
                    strkh_ten = clsCommon.fncCnvNullToString(objData.Rows[0]["kh_ten"]);
                    strkhs_tenkhoas = clsCommon.fncCnvNullToString(objData.Rows[0]["khs_tenkhoas"]);
                    strLop_ten = clsCommon.fncCnvNullToString(objData.Rows[0]["Lop_ten"]);
                    strmo_tenmon = clsCommon.fncCnvNullToString(objData.Rows[0]["mo_tenmon"]);

                    intLyThuyet = clsCommon.fncCnvNullToInt(objData.Rows[0]["LyThuyet"]);
                    intThucHanh = clsCommon.fncCnvNullToInt(objData.Rows[0]["ThucHanh"]);
                    intKiemTra = clsCommon.fncCnvNullToInt(objData.Rows[0]["KiemTra"]);
                    intSoDiemDinhKi = clsCommon.fncCnvNullToInt(objData.Rows[0]["SoDiemDinhKi"]);
                }

                objExcelControl.ActiveWorksheet.Cells["A3"].Value = string.Format("KHOA {0}", strkh_ten.ToUpper());
                objExcelControl.ActiveWorksheet.Cells["A6"].Value = string.Format("Lớp: {0}    Khoá: {1}", strLop_ten, strkhs_tenkhoas);
                objExcelControl.ActiveWorksheet.Cells["A7"].Value = string.Format("Môn-đun, môn học: {0}", strmo_tenmon);
                objExcelControl.ActiveWorksheet.Cells["A8"].Value = string.Format("Tổng số tiết (h): {0}(Trong đó: LT {1}, TH {2}, KT {3})", intLyThuyet + intThucHanh + intKiemTra, intLyThuyet, intThucHanh, intKiemTra);

                for (int i = 0; i <= objData.Rows.Count - 1; i++)
                {
                    //lay data
                    strhs_hodem = clsCommon.fncCnvNullToString(objData.Rows[i]["hs_hodem"]);
                    strhs_ten = clsCommon.fncCnvNullToString(objData.Rows[i]["hs_ten"]);
                    dths_ngaysinh = clsCommon.fncCnvNullToDatetime(objData.Rows[i]["hs_ngaysinh"]);
                    strHSM_ghichu = clsCommon.fncCnvNullToString(objData.Rows[i]["HSM_ghichu"]);

                    fltHSM_diemdinhkylanso1 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso1"]);
                    fltHSM_diemdinhkylanso2 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso2"]);
                    fltHSM_diemdinhkylanso3 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso3"]);
                    fltHSM_diemdinhkylanso4 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso4"]);
                    fltHSM_diemdinhkylanso5 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso5"]);
                    fltHSM_diemdinhkylanso6 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso6"]);
                    fltHSM_diemdinhkylanso7 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso7"]);
                    fltHSM_diemdinhkylanso8 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso8"]);
                    fltHSM_diemdinhkylanso9 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso9"]);
                    fltHSM_diemdinhkylanso10 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemdinhkylanso10"]);
                    fltHSM_diemketthucmodulelan1 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemketthucmodulelan1"]);
                    fltHSM_diemketthucmodulelan2 = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemketthucmodulelan2"]);
                    fltHSM_diemtongket = clsCommon.fncCnvNullToFloat(objData.Rows[i]["HSM_diemtongket"]);

                    //set cell
                    intCurrentCol = 0; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = i + 1;
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = strhs_hodem;
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = strhs_ten;
                    //intCurrentCol++; if (dths_ngaysinh > DateTime.MinValue) objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = dths_ngaysinh.ToString("dd/MM/yyyy");

                    //điểm
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso1.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso2.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso3.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso4.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso5.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso6.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso7.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso8.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso9.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemdinhkylanso10.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemketthucmodulelan1.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemketthucmodulelan2.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = fltHSM_diemtongket.ToString(cstrFloatFormat);
                    intCurrentCol++; objExcelControl.ActiveWorksheet.Cells[intCurrentRow, intCurrentCol].Value = strHSM_ghichu;
                    

                    intCurrentRow++;
                }

                //xoa cot thua
                //hiện tại đang để 10 cột điểm từ D(index = 3) đến M(index = 12)
                //dựa vào SoDiemDinhKi để xóa cột thừa
                for (int i = 12; i > 3+intSoDiemDinhKi-1; i--)
                { 
                    //if(i <= (3 + intSoDiemDinhKi -1))continue;
                    objExcelControl.ActiveWorksheet.Columns[i].Delete();
                }

                ////file to save
                //SaveFileDialog dlgSave = new SaveFileDialog();
                //dlgSave.Filter = "Excel (*.xls)|*.xls|All files (*.*)|*.*";
                //dlgSave.FilterIndex = 1;
                //dlgSave.RestoreDirectory = true;

                ////export word
                //objExcelControl.SaveDocument(dlgSave.FileName, DocumentFormat.Xls);

                return true;
            }
            catch (Exception ex) { clsCommon.fncMsgError(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name, System.Reflection.MethodBase.GetCurrentMethod().ToString(), ex.Message); }
            finally { }
            return blnResult;
        }

    }
}
